package com.moonciki.artadmin.core.utils.excel;

import com.moonciki.artadmin.core.enums.ResponseEnum;
import com.moonciki.artadmin.core.exception.CustomException;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.HashSet;
import java.util.Set;

/**
 * excel 读取工具
 */
@Slf4j
public abstract class ExcelReader {

    /** 批量任务最大条数 **/
    public static final int batch_max_size = 10000;

    public final static String xls = ".xls";
    public final static String xlsx = ".xlsx";

    @Getter
    @Setter
    protected InputStream input;

    /** 列长 **/
    @Getter
    @Setter
    protected int columnLength;

    @Getter
    @Setter
    protected Set<Integer> dateCellSet;

    @Getter
    @Setter
    protected int sheetNumber;

    /** 最大行 **/
    @Getter
    @Setter
    protected int lastRowNum;

    @Getter
    @Setter
    protected Workbook workbook;

    @Getter
    @Setter
    protected Sheet currentSheet;

    @Getter
    @Setter
    protected FormulaEvaluator formulaEvaluator;

    /**
     * 添加字段为时间格式的下标
     * @param index 第几个下标是时间
     */
    public void addDateCell(int index){
        if(dateCellSet == null){
            dateCellSet = new HashSet<>();
        }
        dateCellSet.add(index);
    }

    /**
     * 不通过文件后缀判断文档类型,通过FileMagic判断(更准确),避免用户手动将xls的文件修改后缀为xlsx等问题.
     * @param input
     * @param columnLength
     * @param sheetNumber
     * @return
     * @throws Exception
     */
    public static ExcelReader getExcelReader(InputStream input, int columnLength, int sheetNumber) throws Exception{
        ExcelReader excelReader = null;

        InputStream inputStream = FileMagic.prepareToCheckMagic(input);
        FileMagic fileMagic = FileMagic.valueOf(inputStream);
        if (FileMagic.OOXML.equals(fileMagic)){
            excelReader = new XExcelReader(inputStream, columnLength, sheetNumber);
        }else if (FileMagic.OLE2.equals(fileMagic)){
            excelReader = new HExcelReader(inputStream, columnLength, sheetNumber);
        }else{
            log.error("暂时无法解析文档类型是{}的文件", fileMagic);
            throw CustomException.createException(ResponseEnum.sys_error.info("Not Support Yet!"));
        }
        return excelReader;
    }

    @Deprecated
    public static ExcelReader getExcelReader(InputStream input, String fileName, String[] columnArray, int sheetNumber) throws Exception{
        ExcelReader excelReader = getExcelReader(input, columnArray.length, sheetNumber);
        return excelReader;
    }

    /**
     * 检测excel 标题
     * @param input
     * @param titleArray
     * @return
     */
    public static void checkTemplate(InputStream input, String[] titleArray, int titleRow){
        ExcelReader excelReader = null;

        try {
            if(input == null){
                throw CustomException.createException(ResponseEnum.request_error.info());
            }

            try {
                excelReader = ExcelReader.getExcelReader(input, titleArray.length, 0);
            } catch (Exception e) {
                log.error("Read Excel Error:", e);
                throw CustomException.createException(ResponseEnum.file_template_error.info());
            }

            Object[] titleValueArray = excelReader.readExcel(titleRow);

            boolean checkTmp = Arrays.equals(titleArray, titleValueArray);

            if(!checkTmp){
                throw CustomException.createException(ResponseEnum.file_template_error.info());
            }

            int lastRowNum = excelReader.getLastRowNum();

            if (lastRowNum < 1) {
                throw CustomException.createException(ResponseEnum.file_template_empty.info());
            }

            //excelReader.checkLastRowNum();

        } finally {
            if(excelReader != null){
                excelReader.closeWorkbook();
            }
        }


    }
    public static void checkTemplate(MultipartFile file, String[] titleArray, int titleRow) {
        try {
            InputStream input = file.getInputStream();

            checkTemplate(input, titleArray, 0);
        } catch (IOException e) {
            log.error("Read Excel Error:", e);
            throw CustomException.createException(ResponseEnum.file_template_error.info());
        }
    }

    public void checkLastRowNum(){
        if(lastRowNum > batch_max_size){
            throw CustomException.createException(ResponseEnum.request_error.info("批量最大条数不允许超过" + batch_max_size));
        }
    }

    /**
     * 获取单元格值
     * @param cell
     * @return
     * @throws Exception
     */
    public Object getValue(Cell cell, int cellIndex) {
        Object cellValue = null;

        if (cell.getCellTypeEnum() == CellType.BLANK) {
            cellValue = "";
        } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            cellValue = cell.getBooleanCellValue();
        } else if (cell.getCellTypeEnum() == CellType.ERROR) {
            //null
        } else if(cell.getCellTypeEnum() == CellType.FORMULA){
            //公式,未测试
            cellValue = cell.getCellFormula();

            CellValue evaluate = formulaEvaluator.evaluate(cell);

            cellValue=  getValue(evaluate, cellIndex);
        } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            short dataFormat = cell.getCellStyle().getDataFormat();

            if (DateUtil.isCellDateFormatted(cell)) {
                //时间，日期格式
                cellValue = cell.getDateCellValue();

            }else if(dataFormat == 49){
                //文本格式
                double NumValue = cell.getNumericCellValue();
                BigDecimal bdValue = new BigDecimal(NumValue + "");
                cellValue = bdValue.stripTrailingZeros().toPlainString();
            }else{
                //先直接读成字符串
                double NumValue = cell.getNumericCellValue();
                BigDecimal bdValue = new BigDecimal(NumValue + "");

                cellValue = bdValue.stripTrailingZeros().toPlainString();
            }
        } else {
            cellValue = cell.getStringCellValue();
            if(cellValue != null){
                cellValue = ((String) cellValue).trim();
            }
        }

        return cellValue;
    }

    /**
     * 公式返回的单元格
     * @param evaluate
     * @param cellIndex
     * @return
     */
    public Object getValue(CellValue evaluate, int cellIndex) {
        Object cellValue = null;

        if (evaluate.getCellTypeEnum() == CellType.BLANK) {
            cellValue = "";
        } else if (evaluate.getCellTypeEnum() == CellType.BOOLEAN) {
            cellValue = evaluate.getBooleanValue();
        } else if (evaluate.getCellTypeEnum() == CellType.ERROR) {
            //null
        } else if(evaluate.getCellTypeEnum() == CellType.FORMULA){
            //公式返回的不可能是公式

        } else if (evaluate.getCellTypeEnum() == CellType.NUMERIC) {
            //先直接读成字符串
            double NumValue = evaluate.getNumberValue();
            BigDecimal bdValue = new BigDecimal(NumValue + "");

            cellValue = bdValue.stripTrailingZeros().toPlainString();
        } else {
            cellValue = evaluate.getStringValue();
            if(cellValue != null){
                cellValue = ((String) cellValue).trim();
            }
        }

        return cellValue;
    }

    /**
     * 关闭资源
     */
    public void closeWorkbook(){

        if(workbook != null){
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if(input != null){
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 关闭资源
     */
    public void closeInput(){

        if(workbook != null){
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if(input != null){
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 关闭资源
     */
    public void close(){

        closeWorkbook();

        closeInput();

    }

    /**
     * 读取excel
     * @throws Exception
     */
    public abstract Object[] readExcel(int rowNumber);

}

